#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sqlite3.h"

static int total = 0;
int select_callback(void *data, int col_count, char **col_values, char **col_name);
int exists(sqlite3 *db, int id);

int select_callback(void *data, int col_count, char **col_values, char **col_name)
{
    total = atoi(col_values[0]);

    return total;
}

int main(int argc, char *argv[])
{
    char sql[128];
    sqlite3 *db;
    char *zErrMsg;
    int rc;

    total = 0;
    sqlite3_open("test.db", &db);//open database

    // create a new table
    memset(sql, '\0', 128);
    strcpy(sql, "create table if not exists tb(id INTEGER PRIMARY KEY, name TEXT)");
    rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);
    if(rc != SQLITE_OK)
        printf("Create table error: %s\n", zErrMsg);

    if(!exists(db, 1)) {
        memset(sql, '\0', 128);
        strcpy(sql, "insert into tb(id, name) values(1, 'czx')");
        sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);
        if(rc != SQLITE_OK)
            printf("insert id=1 error: %s\n", zErrMsg);
        total = 0;
    } else {
        printf("id = 1 exists, continue;\n");
    }
    if(!exists(db, 2)) {
        memset(sql, '\0', 128);
        strcpy(sql, "insert into tb(id, name) values(2, 'czx2')");
        sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);
        if(rc != SQLITE_OK)
            printf("insert id=2 error: %s\n", zErrMsg);
        total = 0;
    } else {
        printf("id = 2 exists, continue;\n");
    }

    memset(sql, '\0', 128);
    strcpy(sql, "select count(*) as total from tb where id>3");
    sqlite3_exec(db, sql, select_callback, NULL, &zErrMsg);
    printf("total = %d\n", total);
    if(rc != SQLITE_OK)
        printf("select count(*) error: %s\n", zErrMsg);

    sqlite3_free(zErrMsg);
    sqlite3_close(db);//close database

    return 0;
}

int exists(sqlite3 *db, int id)
{
    char sql[128];
    sprintf(sql, "select count(*) as total from tb where id=%d", id);
    sqlite3_exec(db, sql, select_callback, NULL, NULL);

    return total;
}
